package top.hcode.hoj;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import top.hcode.hoj.common.LazyDao;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 导入江汉区的账号（只含了五年级和六年级的账号）
 * @author orangej
 * @since 2025/5/12
 */
@Slf4j
public class ImportJianghanAccountApp extends BaseDbApp {
    private static LazyDao dao;
    private static final String filePath = "E:\\bookshare\\account";

    public static void main(String[] args) throws Exception {
        dao = buildLazyDao();
        File dir = new File(filePath);
        File[] files = dir.listFiles((d, name) -> name.endsWith(".xlsx"));
        if (files == null) return;

        List<Object[]> insertList = new ArrayList<>();

        for (File file : files) {
            String schoolShortName = file.getName().replace(".xlsx", "");
            String schoolName = dao.fetchString("select org_name from org where org_type = 2 and org_name like ?", "%" + schoolShortName + "%");
            if (schoolName == null) {
                throw new RuntimeException("学校不存在：" + schoolShortName);
            }

            int count = 0;
            try (FileInputStream fis = new FileInputStream(file);
                 Workbook workbook = new XSSFWorkbook(fis)) {
                Sheet sheet = workbook.getSheetAt(0);
                for (int i = 2; i <= sheet.getLastRowNum(); i++) { // 跳过前两行表头
                    Row row = sheet.getRow(i);
                    if (row == null) continue;

                    // 五年级
                    String studentName1 = getCellString(row, 4);
                    if (studentName1 != null && !studentName1.isEmpty()) {
                        insertList.add(new Object[]{
                                schoolName,
                                schoolName, // campus_name
                                formatClassName(5, getCellString(row, 1)), // class_name
                                studentName1,
                                getCellString(row, 2)  // username
                        });
                        count++;
                    }

                    // 六年级
                    String studentName2 = getCellString(row, 10);
                    if (studentName2 != null && !studentName2.isEmpty()) {
                        insertList.add(new Object[]{
                                schoolName,
                                schoolName, // campus_name
                                formatClassName(6, getCellString(row, 7)),  // class_name
                                studentName2,
                                getCellString(row, 8)   // username
                        });
                        count++;
                    }
                }
            }

            log.info("学校:{} 人数:{}", schoolName, count);
        }

        // 批量插入
        if (!insertList.isEmpty()) {
            AtomicInteger count = new AtomicInteger(0);
            CollUtil.split(insertList, 500)
                    .parallelStream()
                    .forEach(list->{
                        dao.batchUpdate("insert into import_student_0228 (school_name, campus_name, class_name, student_name, username) " +
                                        "values (?, ?, ?, ?, ?)", list);
                        log.info("插入:{}条", count.addAndGet(list.size()));
                    });
        }

        // 更新已存在的用户id
        log.info("更新已存在的用户id");
        dao.runSql("update import_student_0228 t0 left join org_user t1 on t0.username = t1.username set t0.user_id = t1.id where t0.user_id is null");

        // 删除已存在的用户信息
        log.info("删除已存在的用户信息");
        dao.runSql("delete from import_student_0228 where user_id is not null");

        // 更新五年级班号
        log.info("更新五年级班号");
        for (int i = 1; i < 20; i++) {
            String className = String.format("%d%02d", 5, i);
            dao.runSql("update import_student_0228 set grade_num = 5," +
                       "class_num = ? where class_name = ?", i, className);
        }

        // 更新六年级班号
        log.info("更新六年级班号");
        for (int i = 1; i < 20; i++) {
            String className = String.format("%d%02d", 6, i);
            dao.runSql("update import_student_0228 set grade_num = 6," +
                       "class_num = ? where class_name = ?", i, className);
        }
    }

    private static String getCellString(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell == null) return null;
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }

    static String formatClassName(int grade, String className) {
        className = className.replace("（", "");
        className = className.replace("）", "");
        className = className.replace("(", "");
        className = className.replace(")", "");
        if (StrUtil.isNumeric(className)) {
            return String.format("%d%02d", grade, Integer.parseInt(className));
        }
        if (ReUtil.isMatch("\\d+班", className)) {
            return String.format("%d%02d", grade, Integer.parseInt(StrUtil.sub(className, 0, -1)));
        }
        if (ReUtil.isMatch("五\\d+", className)) {
            return String.format("%d%02d", grade, Integer.parseInt(className.replace("五", "")));
        }
        if (ReUtil.isMatch("六\\d+", className)) {
            return String.format("%d%02d", grade, Integer.parseInt(className.replace("六", "")));
        }

        return String.format("%d年级%s", grade, className);
    }
}
